IF exists(SELECT * FROM dbo.sysobjects WHERE name='instapOverzichtPersoonlijk' AND xtype='V') 
BEGIN
	DROP VIEW instapOverzichtPersoonlijk
END
GO

create view instapOverzichtPersoonlijk
as
select * from (
select l.voornaam + ' ' + l.achternaam as volleNaam, l.klas, l.klasNr,
   case when charindex('remedi',isNull(n.noodbericht,'')) <> 0 or charindex('clb',isNull(n.noodbericht,'')) <> 0 then  rtrim(ltrim(convert(varchar(8000),n.noodbericht)))  + char(13) + char(10) else '' end +
   case when charindex('remedi',isNull(n.publiekBericht,'')) <> 0  or charindex('clb',isNull(n.publiekbericht,'')) <> 0  then rtrim(ltrim(convert(varchar(8000),n.publiekbericht))) + char(13) + char(10) else '' end +
   case when charindex('remedi',isNull(p.medischeOpmerking,'')) <> 0  or charindex('clb',isNull(p.medischeOpmerking,'')) <> 0 then rtrim(ltrim(convert(varchar(8000),p.medischeOpmerking))) + char(13) + char(10) else '' end +
   case when charindex('remedi',isNull(p.lerenStuderenOpmerking,'')) <> 0  or charindex('clb',isNull(p.lerenStuderenOpmerking,'')) <> 0 then rtrim(ltrim(convert(varchar(8000),p.lerenStuderenOpmerking))) + char(13) + char(10) else '' end +
   case when charindex('remedi',isNull(p.socialeOpmerking,'')) <> 0  or charindex('clb',isNull(p.socialeOpmerking,'')) <> 0 then rtrim(ltrim(convert(varchar(8000),p.socialeOpmerking))) + char(13) + char(10) else '' end +
   case when charindex('remedi',isNull(p.opmerking,'')) <> 0  or charindex('clb',isNull(p.opmerking,'')) <> 0 then rtrim(ltrim(convert(varchar(8000),p.opmerking))) else '' end as tekst 
   from leerlingen..leerlingen as l
	left join leerlingen..persoonlijkeGegevens as p
	on p.inschrijvingsNr = l.inschrijvingsNr
	left join leerlingen..noodbericht as n
	on n.inschrijvingsNr = l.inschrijvingsNr
where l.klas like '1%') as alles

where len(alles.tekst) > 0